"""

@File:db_util.py
@Datetime:2022/11/3 0:10
@Author:wangt
@Desc:
"""
import logging
import pymysql
# 封装数据库操作类



class DBUtil(object):
    # 添加类属性
    conn = None

    @classmethod
    def __get_conn(cls):
        # 判断conn 是否为空，如果是，则创建
        if cls.conn is None:
            cls.conn = pymysql.connect(host='211.103.136.244', port=7061, user='student',
                                       password='iHRM_student_2022', database='ihrm', charset='utf8')
            logging.info("数据库连接成功！")
        # 返回 非空连接
        return cls.conn

    @classmethod
    def __close_conn(cls):
        # 判断：conn 不为空，则需关闭
        if cls.conn is not None:
            cls.conn.close()  # 7.关闭连接
            cls.conn = None

    # 常用方法：查询一条
    @classmethod
    def select_one(cls, sql):
        cursor = None
        res = None
        try:
            # 获取连接
            cls.conn = cls.__get_conn()
            # 获取游标
            cursor = cls.conn.cursor()
            # 执行查询语句
            cursor.execute(sql)
            # 获取结果--提取一条数据
            res = cursor.fetchone()
        except Exception as err:
            logging.info('查询sql错误：', err)
        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接
            cls.__close_conn()
            # 将res结果返回，注意：return后面就不会执行，所以放最后
            return res

    # 常用方法：增删改
    @classmethod
    def uid_db(cls, sql):
        cursor = None
        try:
            # 1.获取连接
            cls.conn = cls.__get_conn()
            # 2.获取游标
            cursor = cls.conn.cursor()
            # 3.执行sql语句
            cursor.execute(sql)
            # print('影响的行数：', cls.conn.affected_rows())
            # logging.info(f'影响的行数：{cls.conn.affected_rows()}')

            # 4.事务提交/回滚
            cls.conn.commit()
        except Exception as err:
            cls.conn.rollback()
            # print('增删改 sql 执行失败：', err)
            logging.error(f'增删改 sql 执行失败: {err}')
        finally:
            # 5.关闭游标
            cursor.close()
            # 6.关闭连接
            cls.__close_conn()


if __name__ == '__main__':
    res = DBUtil.uid_db("delete from bs_user where mobile ='18722220000' ")
    print('结果为：', res)

    # DBUtil.uid_db("update tab set password = '654321' where username ='张3';")
